package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import domain.Piso;

public class JDBCPisosDAO implements IPisosDAO{
	
	private Connection conn;
	
	public JDBCPisosDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
	 protected void finalize() {
	        ConnectionManager.getInstance().checkIn(conn);
	    }

	public void deletePiso(Piso piso) {
		String sql = "DELETE FROM piso WHERE (referencia = ?)";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, piso.getReferencia());

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }	
		
	}

	public void insertPiso(Piso piso, String oid) {
		String sql = "INSERT INTO piso (OID, referencia,nombre,direccion,ciudad," +
				"pais,tipo,precio)VALUES (?,?,?,?,?,?,?,?)";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1,oid);
            stmt.setString(2, piso.getReferencia());
            stmt.setString(3, piso.getNombre());
            stmt.setString(4, piso.getDireccion());
            stmt.setString(5, piso.getCiudad());
            stmt.setString(6, piso.getPais());
            stmt.setString(7, piso.getTipo());
            stmt.setInt(8, piso.getPrecio());

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }	
	}

	public List<Piso> pisosLibres() {
		
		List<Piso> libres = new ArrayList<Piso>();
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		
		String sql = "SELECT * FROM piso WHERE estado= 0";
		
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Piso piso = new Piso(result.getString(2),result.getString(4),
						result.getString(5),result.getString(7),result.getInt(8));
				libres.add(piso);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());

		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}

		return libres;
	}

	public void ocuparPiso(Piso piso,String oid) {
		String sql = "UPDATE piso SET estado = 1 WHERE oid = ?";
		
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,oid);
			stmt.executeUpdate();
		}
	catch (SQLException e) {
		
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			}
		}
	}

	
	public String obtenerOID(String referencia) {
		String res = "";
		String sql = "SELECT OID FROM piso WHERE referencia =?";
		PreparedStatement stmt = null;
		ResultSet result = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, referencia);

			result = stmt.executeQuery();

			if (result.next()) {
				res = result.getString(1);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());

		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
		return res;	
	}

	
	public void liberarPiso(Piso piso, String oid) {
String sql = "UPDATE piso SET estado = 0 WHERE oid = ?";
		
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,oid);
			stmt.executeUpdate();
		}
	catch (SQLException e) {
		
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			}
		}
		
	}

}
